﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DBUtility;
using System.Data.OracleClient;
namespace OracleDAL.Sec
{
    public class Unit
    {
        private static System.Data.DataRow GetByUnitISN(string UnitISN)
        {
            System.Data.DataRow dr = null;
            string sql = "SELECT * FROM sec_Unit WHERE UNITISN='" + UnitISN + "'";
            System.Data.DataSet ds = DBUtility.OracleHelper.Query(sql);
            if (ds.Tables[0].Rows.Count > 0)
                dr = ds.Tables[0].Rows[0];
            return dr;
        }
        public bool DeleteUnit(string UnitISN)
        {
            string sql = "DELETE FROM sec_Unit WHERE UNITISN = '" + UnitISN + "'";
            int effectRows = DBUtility.OracleHelper.ExecuteSql(sql);
            if (effectRows > 0)
                return true;
            else
                return false;
        }
       
        /// <summary>
        /// 增加一条记录
        /// </summary>
        /// <param name="unit"></param>
        /// <returns></returns>
        public bool addUnit(Model.Sec.Unit unit)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into sec_unit(UNITISN,UPNODEID,UNITNAME,CREATETIME,UPNODENAME,ADDRESS,TEL,DESCRIPTION,UNITSORT,BELONGSYSTEM,NODEINDEX,REDIRECTUNIT,REDIRECTUNITNAME,STATUS,ISMONITOR,UPNODEISN) values ");
            sql.Append(" ('" + unit.UnitISN + "','" + unit.UpNodeID + "','" + unit.UnitName + "',to_date('" + unit.Createtime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-MM-dd hh24:mi:ss'), ");
            sql.Append(" '" + unit.UpNodeName + "','" + unit.Address + "','" + unit.Tel + "','" + unit.Description + "','" + unit.UnitSort + "','" + unit.BelongSystem + "'," + unit.NodeIndex + ",'" + unit.RedirecUnit + "',");
            sql.Append(" '" + unit.RedirecUnitName + "','" + unit.Status + "','" + unit.IsMonitor + "','" + unit.UpNodeISN + "')");
            int row = DBUtility.OracleHelper.ExecuteSql(sql.ToString());
            if (row > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Model.Sec.Unit unit)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into SEC_UNIT(");
            strSql.Append("UNITISN,BELONGSYSTEM,NODEINDEX,REDIRECTUNIT,REDIRECTUNITNAME,STATUS,ISMONITOR,UPNODEID,UNITNAME,CREATETIME,UPNODENAME,ADDRESS,TEL,DESCRIPTION,UNITSORT,UPNODEISN)");
            strSql.Append(" values (");
            strSql.Append(":UNITISN,:BELONGSYSTEM,:NODEINDEX,:REDIRECTUNIT,:REDIRECTUNITNAME,:STATUS,:ISMONITOR,:UPNODEID,:UNITNAME,:CREATETIME,:UPNODENAME,:ADDRESS,:TEL,:DESCRIPTION,:UNITSORT,:UPNODEISN)");
            OracleParameter[] parameters = {
					new OracleParameter(":UNITISN", OracleType.VarChar,50),
					new OracleParameter(":BELONGSYSTEM", OracleType.VarChar,200),
					new OracleParameter(":NODEINDEX", OracleType.Number,4),
					new OracleParameter(":REDIRECTUNIT", OracleType.VarChar,50),
					new OracleParameter(":REDIRECTUNITNAME", OracleType.VarChar,80),
					new OracleParameter(":STATUS", OracleType.VarChar,1),
					new OracleParameter(":ISMONITOR", OracleType.VarChar,1),
					new OracleParameter(":UPNODEID", OracleType.VarChar,50),
					new OracleParameter(":UNITNAME", OracleType.VarChar,80),
					new OracleParameter(":CREATETIME", OracleType.DateTime),
					new OracleParameter(":UPNODENAME", OracleType.VarChar,80),
					new OracleParameter(":ADDRESS", OracleType.VarChar,200),
					new OracleParameter(":TEL", OracleType.VarChar,20),
					new OracleParameter(":DESCRIPTION", OracleType.VarChar,500),
					new OracleParameter(":UNITSORT", OracleType.VarChar,80),
                    new OracleParameter(":UPNODEISN", OracleType.VarChar,20)};
            parameters[0].Value = unit.UnitISN;
            parameters[1].Value = unit.BelongSystem;
            parameters[2].Value = unit.NodeIndex;
            parameters[3].Value = unit.RedirecUnit;
            parameters[4].Value = unit.RedirecUnitName;
            parameters[5].Value = unit.Status;
            parameters[6].Value = unit.IsMonitor;
            parameters[7].Value = unit.UpNodeID;
            parameters[8].Value = unit.UnitName;
            parameters[9].Value = unit.Createtime.ToString("yyyy-MM-dd HH:mm:ss");
            parameters[10].Value = unit.UpNodeName;
            parameters[11].Value = unit.Address;
            parameters[12].Value = unit.Tel;
            parameters[13].Value = unit.Description;
            parameters[14].Value = unit.UnitSort;
            parameters[15].Value = unit.UpNodeISN;
             int rows =OracleHelper.ExecuteSql(strSql.ToString(), parameters);
             if (rows > 0)
                 return true;
             else
                 return false;
            
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.Sec.Unit unit)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update SEC_UNIT set ");
            strSql.Append("BELONGSYSTEM=:BELONGSYSTEM,");
            strSql.Append("NODEINDEX=:NODEINDEX,");
            strSql.Append("REDIRECTUNIT=:REDIRECTUNIT,");
            strSql.Append("REDIRECTUNITNAME=:REDIRECTUNITNAME,");
            strSql.Append("STATUS=:STATUS,");
            strSql.Append("ISMONITOR=:ISMONITOR,");
            strSql.Append("UPNODEID=:UPNODEID,");
            strSql.Append("UNITNAME=:UNITNAME,");
            strSql.Append("CREATETIME=:CREATETIME,");
            strSql.Append("UPNODENAME=:UPNODENAME,");
            strSql.Append("ADDRESS=:ADDRESS,");
            strSql.Append("TEL=:TEL,");
            strSql.Append("DESCRIPTION=:DESCRIPTION,");
            strSql.Append("UNITSORT=:UNITSORT,");
            strSql.Append("UPNODEISN=:UPNODEISN");
            strSql.Append(" where UNITISN=:UNITISN ");
            OracleParameter[] parameters = {
					new OracleParameter(":UNITISN", OracleType.VarChar,50),
					new OracleParameter(":BELONGSYSTEM", OracleType.VarChar,200),
					new OracleParameter(":NODEINDEX", OracleType.Number,4),
					new OracleParameter(":REDIRECTUNIT", OracleType.VarChar,50),
					new OracleParameter(":REDIRECTUNITNAME", OracleType.VarChar,80),
					new OracleParameter(":STATUS", OracleType.VarChar,1),
					new OracleParameter(":ISMONITOR", OracleType.VarChar,1),
					new OracleParameter(":UPNODEID", OracleType.VarChar,50),
					new OracleParameter(":UNITNAME", OracleType.VarChar,80),
					new OracleParameter(":CREATETIME", OracleType.DateTime),
					new OracleParameter(":UPNODENAME", OracleType.VarChar,80),
					new OracleParameter(":ADDRESS", OracleType.VarChar,200),
					new OracleParameter(":TEL", OracleType.VarChar,20),
					new OracleParameter(":DESCRIPTION", OracleType.VarChar,500),
					new OracleParameter(":UNITSORT", OracleType.VarChar,80),
                    new OracleParameter(":UPNODEISN", OracleType.VarChar,20)};              
            parameters[0].Value = unit.UnitISN;
            parameters[1].Value = unit.BelongSystem;
            parameters[2].Value = unit.NodeIndex;
            parameters[3].Value = unit.RedirecUnit;
            parameters[4].Value = unit.RedirecUnitName;
            parameters[5].Value = unit.Status;
            parameters[6].Value = unit.IsMonitor;
            parameters[7].Value = unit.UpNodeID;
            parameters[8].Value = unit.UnitName;
            parameters[9].Value = unit.Createtime.ToString("yyyy-MM-dd HH:mm:ss");
            parameters[10].Value = unit.UpNodeName;
            parameters[11].Value = unit.Address;
            parameters[12].Value = unit.Tel;
            parameters[13].Value = unit.Description;
            parameters[14].Value = unit.UnitSort;
            parameters[15].Value = unit.UpNodeISN;
            int rows = OracleHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
                return true;
            else
                return false;
        }
       
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool DelBYWhere(string strwhere)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete SEC_UNIT ");
            if (strwhere.Trim() != "")
            {
                strSql.Append(" where ");
                strSql.Append(strwhere);
                int rows = DBUtility.OracleHelper.ExecuteSql(strSql.ToString());
                if (rows > 0)
                    return true;
            }
            return false;
        }

     
        //public Model.Sec.Unit GetModel(string UnitISN)
        //{
        //    System.Data.DataRow dr = null;
        //    string sql = "SELECT * FROM Unit WHERE UNITISN='" + UnitISN + "'";
        //    System.Data.DataSet ds = DBUtility.OracleHelper.Query(sql);
        //    if (ds.Tables[0].Rows.Count > 0)
        //        dr = ds.Tables[0].Rows[0];

        //    Model.Sec.Unit unit = new Model.Sec.Unit();

        //    unit.UnitISN = UnitISN;
        //    unit.UpNodeName = dr["UPNODENAME"].ToString();
        //    unit.Address = dr["ADDRESS"].ToString();
        //    unit.Createtime = Convert.ToDateTime(dr["CREATETIME"]);
        //    unit.Description = dr["DESCRIPTION"].ToString();
        //    unit.Tel = dr["TEL"].ToString();
        //    unit.UnitName = dr["UNITTIME"].ToString();
        //    unit.UnitSort = dr["UNITSORT"].ToString();
        //    //this.UnitType = dr["UNITTYPE"].ToString();
        //    unit.UpNodeID = dr["UPNODEISN"].ToString();
        //    //unit.PropertyInitialized = true;

        //    return unit;
        //}
        /// <summary>
        /// 返回model
        /// </summary>
        /// <param name="UNITISN"></param>
        /// <returns></returns>
        public Model.Sec.Unit GetModel(string UNITISN)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" from SEC_Unit ");
            strSql.Append(" where UNITISN='" + UNITISN + "' ");
            Model.Sec.Unit model = new Model.Sec.Unit();
            DataSet ds = DBUtility.OracleHelper.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.UnitISN = ds.Tables[0].Rows[0]["UNITISN"].ToString();
                model.BelongSystem = ds.Tables[0].Rows[0]["BELONGSYSTEM"].ToString();
                if (ds.Tables[0].Rows[0]["NODEINDEX"].ToString() != "")
                {
                    model.NodeIndex = int.Parse(ds.Tables[0].Rows[0]["NODEINDEX"].ToString());
                }
                model.RedirecUnit = ds.Tables[0].Rows[0]["REDIRECTUNIT"].ToString();
                model.RedirecUnitName = ds.Tables[0].Rows[0]["REDIRECTUNITNAME"].ToString();
                model.Status = ds.Tables[0].Rows[0]["STATUS"].ToString();
                model.IsMonitor = ds.Tables[0].Rows[0]["ISMONITOR"].ToString();
                model.UpNodeID = ds.Tables[0].Rows[0]["UPNODEID"].ToString();
                model.UnitName = ds.Tables[0].Rows[0]["UNITNAME"].ToString();
                if (ds.Tables[0].Rows[0]["CREATETIME"].ToString() != "")
                {
                    model.Createtime = DateTime.Parse(ds.Tables[0].Rows[0]["CREATETIME"].ToString());
                }

                model.UpNodeName = ds.Tables[0].Rows[0]["UPNODENAME"].ToString();
                model.Address = ds.Tables[0].Rows[0]["ADDRESS"].ToString();
                model.Tel = ds.Tables[0].Rows[0]["TEL"].ToString();
                model.Description = ds.Tables[0].Rows[0]["DESCRIPTION"].ToString();
                model.UnitSort = ds.Tables[0].Rows[0]["UNITSORT"].ToString();
                model.UpNodeISN = ds.Tables[0].Rows[0]["UPNODEISN"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }
        public bool exist(string strWhere)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from sec_unit where ");
            sql.Append(strWhere);
            OracleDataReader dr = OracleHelper.ExecuteReader(sql.ToString());
            int rows = 0;
            while (dr.Read())
                rows++;
            if (rows > 0)
                return true;
            else
                return false;
        }
        public Model.Sec.Unit GetModelBYWhere(string strWhere)
        {  
            StringBuilder sql=new StringBuilder();
            sql.Append("select *  from sec_unit ");
            if (strWhere.Trim() != "")
            {
                sql.Append(" where " + strWhere);
            }
            DataSet ds = OracleHelper.Query(sql.ToString());
            Model.Sec.Unit model = new Model.Sec.Unit();
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.UnitISN = ds.Tables[0].Rows[0]["UNITISN"].ToString();
                model.BelongSystem = ds.Tables[0].Rows[0]["BELONGSYSTEM"].ToString();
                if (ds.Tables[0].Rows[0]["NODEINDEX"].ToString() != "")
                {
                    model.NodeIndex = int.Parse(ds.Tables[0].Rows[0]["NODEINDEX"].ToString());
                }
                model.RedirecUnit = ds.Tables[0].Rows[0]["REDIRECTUNIT"].ToString();
                model.RedirecUnitName = ds.Tables[0].Rows[0]["REDIRECTUNITNAME"].ToString();
                model.Status = ds.Tables[0].Rows[0]["STATUS"].ToString();
                model.IsMonitor = ds.Tables[0].Rows[0]["ISMONITOR"].ToString();
                model.UpNodeID = ds.Tables[0].Rows[0]["UPNODEID"].ToString();
                model.UnitName = ds.Tables[0].Rows[0]["UNITNAME"].ToString();
                if (ds.Tables[0].Rows[0]["CREATETIME"].ToString() != "")
                {
                    model.Createtime = DateTime.Parse(ds.Tables[0].Rows[0]["CREATETIME"].ToString());
                }
                model.UpNodeISN = ds.Tables[0].Rows[0]["UpNodeISN"].ToString();
                model.UpNodeName = ds.Tables[0].Rows[0]["UPNODENAME"].ToString();
                model.Address = ds.Tables[0].Rows[0]["ADDRESS"].ToString();
                model.Tel = ds.Tables[0].Rows[0]["TEL"].ToString();
                model.Description = ds.Tables[0].Rows[0]["DESCRIPTION"].ToString();
                model.UnitSort = ds.Tables[0].Rows[0]["UNITSORT"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 移动相邻单位
        /// </summary>
        /// <param name="upNodeid"></param>
        /// <param name="i"></param>
        /// <returns></returns>
        public bool moveUnit(string upNodeid,int i)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update sec_unit set nodeindex=");
            sql.Append(i);
            sql.Append(" where nodeindex=");
            sql.Append(i+1);
            sql.Append(" and upnodeid='");
            sql.Append(upNodeid);
             sql.Append("'");
            int rows = OracleHelper.ExecuteSql(sql.ToString());
            if (rows > 0)
                return true;
            else
                return false;
        }
        public int getCount(string strWhere)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from sec_unit where ");
            sql.Append(strWhere);
            int i = 0;
            OracleDataReader dr = OracleHelper.ExecuteReader(sql.ToString());
            while(dr.Read())
                i++;
            return i;
           
        }
        public List<Model.Sec.Unit> GetAll(string unitisn)
        {
            string sql = "select * from sec_unit where unitisn='" + unitisn + "'";
            System.Data.OracleClient.OracleDataReader odr = DBUtility.OracleHelper.ExecuteReader(sql);
            List<Model.Sec.Unit> list = new List<Model.Sec.Unit>();
            while (odr.Read())
            {
                Model.Sec.Unit ug = new Model.Sec.Unit();
                ug.UnitName = odr["UNITNAME"].ToString();
                ug.Address = odr["ADDRESS"].ToString();
                ug.Tel = odr["TEL"].ToString();
                ug.UnitSort = odr["UNITSORT"].ToString();
                ug.UpNodeName = odr["UPNODENAME"].ToString();
                ug.BelongSystem = odr["BELONGSYSTEM"].ToString();
                if ( odr["CREATETIME"].ToString() != "")
                {
                    ug.Createtime = DateTime.Parse( odr["CREATETIME"].ToString());
                }
                if (odr["NODEINDEX"].ToString() != "")
                {
                    ug.NodeIndex = int.Parse(odr["NODEINDEX"].ToString());
                }
                
                ug.Description = odr["DESCRIPTION"].ToString();
                ug.IsMonitor = odr["ISMONITOR"].ToString();
               
                ug.RedirecUnit = odr["REDIRECTUNIT"].ToString();
                ug.RedirecUnitName = odr["REDIRECTUNITNAME"].ToString();
                ug.Status = odr["STATUS"].ToString();
                ug.UnitISN = odr["UNITISN"].ToString();           
                ug.UpNodeID = odr["UPNODEID"].ToString();
                ug.UpNodeISN = odr["UPNODEISN"].ToString(); 
                list.Add(ug);
            }
            return list;
        }
        /// <summary>
        /// 返回dataread中单位ISN和单位名称
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public OracleDataReader returnDataReader(string strWhere)
        {
            string sql = "select unitisn,unitname from sec_unit where " + strWhere;
            OracleDataReader dr = OracleHelper.ExecuteReader(sql);
            return dr;
        }
        /// <summary>
        /// 返回单位ISN和单位名称,和上级NodeID
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable getTable(string strWhere)
        {
            string sql = "select unitisn,unitname,upnodeid from sec_unit where " + strWhere;
            return OracleHelper.Query(sql).Tables[0];
        }
    }
}